/**
 * 	 DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 *   @author Colin Redmond
 *   Copyright (C) 2011 Colin Redmond
 *
 *   Licensed under the Apache License, Version 2.0 (the "License");
 *   you may not use this file except in compliance with the License.
 *   You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *   Unless required by applicable law or agreed to in writing, software
 *   distributed under the License is distributed on an "AS IS" BASIS,
 *   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *   See the License for the specific language governing permissions and
 *   limitations under the License.
 */

package com.cred.industries.platform.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TimeZone;

import net.sf.ehcache.Cache;
import net.sf.ehcache.Element;

import org.joda.time.DateTime;
import org.joda.time.DateTimeZone;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.cred.industries.core.dao.cache.CacheMangerDAO;
import com.cred.industries.core.database.DBConnectionManager;
import com.cred.industries.core.exceptions.ApplicationException;
import com.cred.industries.core.transferObj.result.ReturnCodes;
import com.cred.industries.platform.business.objects.MetricsBO;
import com.cred.industries.platform.dao.cache.MetricsCacheManager;


/**
 * @author Colin Redmond
 * Copyright TBA Digital 2011
 * 
 * manages all DB interaction for the metrics. 
 * Note this class has no modifier so it is only accessible in this package
 */
public class MetricsMySQLDAO implements IMetricsDAO {

	private final static Logger logger = LoggerFactory.getLogger(MetricsMySQLDAO.class);
	
	private static final String SOURCE_CUSTOMER = "sourceCustomerId";
	private static final String TARGET_CUSTOMER = "targetCustomerId";
	private static final String METRIC_TIME = "time";
	private static final String METRIC_TYPE_NAME = "metricTypeName";
	private static final String METRIC_TYPE_ID = "metricTypeId";
	private static final String METRIC_INT_DATA = "intData";
	private static final String METRIC_VALUE = "metricValue";
	private static final String METRIC_VALUE_ID = "metricValueId";
	private static final String METRIC_VALUE_NAME = "metricValuename";
	
	//cache names
	private static final String METRIC_TYPE_CACHE = "metricsType";
	private static final String METRIC_VALUE_NAME_CACHE = "metricsValueName";
	
	/* (non-Javadoc)
	 * @see com.cred.industries.platform.dao.IMetricsDOA#insert(com.cred.industries.platform.business.objects.MetricsBO)
	 */
	@Override
	public void insert(MetricsBO metric) {
		
		insert(metric, false);
		
	}
	
	@Override
	public void insert(MetricsBO metric, boolean dontCache) {
		
		if(dontCache) {
			ArrayList<MetricsBO> metrics = new ArrayList<>();
			metrics.add(metric);
			insert(metrics);
		} else {
			MetricsCacheManager.get().cacheMetric(metric);
		}
	}
	
	/* (non-Javadoc)
	 * @see com.cred.industries.platform.dao.IMetricsDOA#insert(java.sql.Connection, com.cred.industries.platform.business.objects.MetricsBO)
	 */
	@Override
	public void insert(List<MetricsBO> metricsBO) {
		
		//logger.debug("writing out batch" );
		
		try ( Connection connect = DBConnectionManager.getInstance().getConnection()){
			
			connect.setAutoCommit(false);
			
			try {
				insertMetrics(connect, metricsBO);
				insertIntMetric(connect, metricsBO, true);
				insertFloatMetric(connect, metricsBO);
				insertStringMetric(connect, metricsBO);
				
				connect.commit();
			} catch (Exception e) {
				connect.rollback();
				throw e;
			}
			
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.DATABASE_ERROR, "unable to update DB when creating new metric ");
		}
	}
	
	
	 
	private void insertMetrics(Connection connect, List<MetricsBO> metricsBO) {
		 
		boolean doBatch = metricsBO.size() > 1;
		boolean doBatchSubmit = false;
		String sqlQuery = "INSERT INTO metrics VALUES(?,?,?,?,?,?)";
		 
		try (PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery, Statement.RETURN_GENERATED_KEYS)) {
			preparedStatement.setInt(1, 0);
				
			for(MetricsBO metric : metricsBO) {
				
				int metricTypeid = getMetricTypeId(connect, metric.getMetricType(), true);
				
				preparedStatement.setInt(2, metricTypeid);
				
				if(metric.getSourceCustId() != -1) {
					preparedStatement.setInt(3, metric.getSourceCustId() );
				} else {
					preparedStatement.setNull(3, Types.INTEGER);
				}
				
				if(metric.getTargetCustId() != -1) {
					preparedStatement.setInt(4, metric.getTargetCustId() );
				} else {
					preparedStatement.setNull(4, Types.INTEGER);
				}
				
				//as an optimization we store 1 int data with the main metrics. 
				//since a lot of our metrics are just 1 int this should speed it up a lot
				if(!metric.getIntMetrics().isEmpty()) {
					
					// get the first item from the map
					Map.Entry<String, Integer> entry = metric.getIntMetrics().entrySet().iterator().next();
					//add the first item to the preparedStatement
					preparedStatement.setInt(5, entry.getValue() );
				} else {
					preparedStatement.setNull(5, Types.INTEGER);
				}
				
				Timestamp issued = new Timestamp(metric.getTime().getMillis());
				preparedStatement.setTimestamp(6, issued, Calendar.getInstance(TimeZone.getTimeZone("UTC")));
				
				//if we don't need the generated key since we aren't adding any 
				//extra associated data, just batch it. 
				if(doBatch && metric.getFloatMetrics().size() == 0 && metric.getStringMetrics().size() == 0 && metric.getIntMetrics().size() <= 1) {
					preparedStatement.addBatch();
					doBatchSubmit = true;
				} else {
					preparedStatement.executeUpdate();
					
					int metricInstanceId = 0;
					
					try (ResultSet resultSet = preparedStatement.getGeneratedKeys()) {
					
						if(resultSet.next()) {
							metricInstanceId = resultSet.getInt(1);
							metric.setMetricInstanceId(metricInstanceId);
						} else {
							throw new ApplicationException(ReturnCodes.DATABASE_ERROR, "no metricInstanceId generated " + metric.getMetricType());
						}
					}
				}
			}
			
			//if we have batch data to submit it now.
			if(doBatchSubmit) {
				int[] updatedBatch = preparedStatement.executeBatch();
				boolean updated = validateBatchUpdate(updatedBatch);
				
				if(updated == false) {
					throw new ApplicationException(ReturnCodes.DATABASE_ERROR, "error while inserting metrics");
				}
			}
			
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.DATABASE_ERROR,  "unable to inserting metrics");
		} 
	}	 
	
	/**
	 * inserts a int metric value that is associated with a new metric. 
	 * @param connect sql connection 
	 * @param metricInstanceId id of the new metric
	 * @param intMetrics the set of int metrics
	 */
	private void insertIntMetric(Connection connect, List<MetricsBO> metricsBO, boolean skipFirst) {
		
		String sqlQuery = "INSERT INTO metricsint values(?,?,?)";
		try (PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery)){ 
			
			for(MetricsBO metric : metricsBO) {
				
				boolean lSkipFirst = skipFirst;
				
				for(Map.Entry<String, Integer> entry : metric.getIntMetrics().entrySet()) {
					
					//skip the first item if needed
					if(lSkipFirst) {
						lSkipFirst = false;
						continue;
					}
					
					int metricValueId = getMetricValueId(connect, entry.getKey(), true);
					preparedStatement.setInt(1, metric.getMetricInstanceId());
					preparedStatement.setInt(2, entry.getValue());
					preparedStatement.setInt(3, metricValueId);
					
					preparedStatement.addBatch();
				}
				
				int[] updatedBatch = preparedStatement.executeBatch();
				boolean updated = validateBatchUpdate(updatedBatch);
				
				if(updated == false)
					throw new ApplicationException(ReturnCodes.DATABASE_ERROR, "no rows changed when creating new metricsint ");
			}
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.DATABASE_ERROR,  "unable to insert metricsint ");
		} 
	}
	
	/**
	 * inserts a float metric value that is associated with a new metric. 
	 * @param connect sql connection 
	 * @param metricInstanceId id of the new metric
	 * @param floatMetrics float data value associated with the new metric
	 */
	private void insertFloatMetric(Connection connect, List<MetricsBO> metricsBO) {
			
		String sqlQuery = "INSERT INTO metricsfloat values(?,?,?)";
		try (PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery)){ 
			
			for(MetricsBO metric : metricsBO) {
				
				for(Map.Entry<String, Float> entry : metric.getFloatMetrics().entrySet()) {
								
					int metricValueId = getMetricValueId(connect, entry.getKey(), true);
					preparedStatement.setInt(1, metric.getMetricInstanceId());
					preparedStatement.setFloat(2, entry.getValue());
					preparedStatement.setInt(3, metricValueId);
					
					preparedStatement.addBatch();
				}
			}
			
			int[] updatedBatch = preparedStatement.executeBatch();
			boolean updated = validateBatchUpdate(updatedBatch);
			
			if(updated == false)
				throw new ApplicationException(ReturnCodes.DATABASE_ERROR, "no rows changed when creating new metricsint ");
			
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.DATABASE_ERROR,  "unable to insert metricsfloat ");
		}
	}
	
	
	/**
	 * inserts a string metric value that is associated with a new metric. 
	 * @param connect sql connection 
	 * @param metricInstanceId id of the new metric
	 * @param floatMetrics string data value associated with the new metric
	 */
	private void insertStringMetric(Connection connect, List<MetricsBO> metricsBO) {
			
		String sqlQuery = "INSERT INTO metricsstring values(?,?,?)";

		try (PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery)){ 
			
			for(MetricsBO metric : metricsBO) {
				
				for(Map.Entry<String, String> entry : metric.getStringMetrics().entrySet()) {
								
					int metricValueId = getMetricValueId(connect, entry.getKey(), true);
					preparedStatement.setInt(1, metric.getMetricInstanceId());
					preparedStatement.setString(2, entry.getValue());
					preparedStatement.setInt(3, metricValueId);
					
					preparedStatement.addBatch();
				}
			}
			
			int[] updatedBatch = preparedStatement.executeBatch();
			boolean updated = validateBatchUpdate(updatedBatch);
					
			if(updated == false)
				throw new ApplicationException(ReturnCodes.DATABASE_ERROR, "no rows changed when creating new metricsint ");
			
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.DATABASE_ERROR,  "unable to insert metricsstring ");
		}
	}
	
	/**
	 * Will look in the DB for a metric type with the name provided. If not found it 
	 * will create one
	 * @param connect sql connection
	 * @param name name of the metric type
	 * @param createIfNotExists create a new entry if one doesn't already exist
	 * @return id of the metric type
	 */
	private int getMetricTypeId(Connection connect, String metricType, boolean createIfNotExists) {
		
		Cache typeCache = CacheMangerDAO.getCache(METRIC_TYPE_CACHE);
		Element element = typeCache.get(metricType);
		if(element != null) {
			return (Integer) element.getValue();
		}
		
		String sqlQuery = "SELECT " + METRIC_TYPE_ID + " FROM  metricstype WHERE " + METRIC_TYPE_NAME + "=?";
		try (PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery)){ 
		
			//logger.warn("metricType " + metricType + " not found in cache");

			preparedStatement.setString(1, metricType);
			
			try(ResultSet resultSet = preparedStatement.executeQuery()) {
				if(resultSet.next()) {
					int foundMetricType = resultSet.getInt(METRIC_TYPE_ID);
					typeCache.put(new Element(metricType, foundMetricType));
					return foundMetricType;
				}
			}
			
			if(createIfNotExists) {
				int newMetricType = createMetricTypeId(connect, metricType);
				typeCache.put(new Element(metricType, newMetricType));
				return newMetricType;
			}
			
			throw new ApplicationException(ReturnCodes.DATABASE_ERROR, "unable to get metric type id" + metricType );
			
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.DATABASE_ERROR, "unable to get/insert metric type id" + metricType );
		}
	}
	
	/**
	 * creates a new metric type
	 * @param connect sql connection
	 * @param metricType name of the metric type we want to create
	 * @return id of the new metric type
	 */
	private int createMetricTypeId(Connection connect, String metricType) {
		
		String sqlQuery = "INSERT INTO  metricstype VALUES(?,?)";
		try (PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery, Statement.RETURN_GENERATED_KEYS)){ 
		
			preparedStatement.setInt(1, 0);
			preparedStatement.setString(2, metricType);
			
			int updated = preparedStatement.executeUpdate();
			
			if(updated == 0)
				throw new ApplicationException(ReturnCodes.DATABASE_ERROR, "no rows changed when creating new MetricTypeId " + metricType);
			
			try (ResultSet resultSet = preparedStatement.getGeneratedKeys()) {
				if(resultSet.next()) {
					 return resultSet.getInt(1);
				} else {
					throw new ApplicationException(ReturnCodes.DATABASE_ERROR, "no MetricTypeId generated " + metricType);
				}
			}
			
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.DATABASE_ERROR, "unable to get/insert metric type id" + metricType );
		}
	}
	
	public static boolean validateBatchUpdate(int[] batchUpdate) {
		 
		 for (int i=0; i<batchUpdate.length; i++) {
		    	
			 if (batchUpdate[i] == Statement.SUCCESS_NO_INFO) {

				 logger.debug("received a batchUpdate = Statement.SUCCESS_NO_INFO");
				 return false;
			 }
			 else if (batchUpdate[i] == Statement.EXECUTE_FAILED) {
				 
				 logger.debug("received a batchUpdate = Statement.EXECUTE_FAILED");
				 return false;
				
			 }
		 }
		 return true;
	}
	
	/**
	 * Will look in the DB for a metric type with the name provided. If not found it 
	 * will create one
	 * @param connect sql connection
	 * @param name name of the metric type
	 * @param createIfNotExists create a new entry if one doesn't already exist
	 * @return id of the metric type
	 */
	private int getMetricValueId(Connection connect, String metricValuename, boolean createIfNotExists) {
		
		Cache typeCache = CacheMangerDAO.getCache(METRIC_VALUE_NAME_CACHE);
		Element element = typeCache.get(metricValuename);
		if(element != null) {
			return (Integer) element.getValue();
		}
		
		String sqlQuery = "SELECT " + METRIC_VALUE_ID + " FROM  metricsvaluename WHERE " + METRIC_VALUE_NAME + "=?";
		try (PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery)){ 

			//logger.warn("metricValuename " + metricValuename + " not found in cache");

			preparedStatement.setString(1, metricValuename);
			
			try (ResultSet resultSet = preparedStatement.executeQuery()) {
				if(resultSet.next()) {
					int foundValueName = resultSet.getInt(METRIC_VALUE_ID);
					typeCache.put(new Element(metricValuename, foundValueName));
					return foundValueName;
				}
			}
			
			if(createIfNotExists) {
				int createdValueName = createMetricValueId(connect, metricValuename);
				typeCache.put(new Element(metricValuename, createdValueName));
				return createdValueName;
			} 
			
			throw new ApplicationException(ReturnCodes.DATABASE_ERROR, "unable to get metric value id" + metricValuename );			
			
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.DATABASE_ERROR, "unable to get/insert metric type id" + metricValuename );
		}
	}   
	
	/**
	 * creates a new metric value name with an id
	 * @param connect sql connection 
	 * @param metricValuename name of the new metric value
	 * @return id of the new metric value
	 */
	private int createMetricValueId(Connection connect, String metricValuename) {
		
		String sqlQuery = "INSERT INTO  metricsvaluename VALUES(?,?)";
		try (PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery, Statement.RETURN_GENERATED_KEYS)) { 
			
			//if we don't find the value lets create one
			preparedStatement.setInt(1, 0);
			preparedStatement.setString(2, metricValuename);
			
			int updated = preparedStatement.executeUpdate();
			
			if(updated == 0)
				throw new ApplicationException(ReturnCodes.DATABASE_ERROR, "no rows changed when creating new MetricValueId " + metricValuename);
			
			try (ResultSet resultSet = preparedStatement.getGeneratedKeys()) {
				if(resultSet.next()) {
					 return resultSet.getInt(1);
				} else {
					throw new ApplicationException(ReturnCodes.DATABASE_ERROR, "no metricTypeId generated " + metricValuename);
				}
			}
			
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.DATABASE_ERROR, "unable to get/insert metric type id" + metricValuename );
		}
	}   
	
	@Override
	public MetricsBO findMetric(int metricInstanceId) {
		
		String sqlQuery = "SELECT * FROM metrics INNER JOIN metricstype "
				  + " WHERE metrics.metricTypeId = metricstype.metricTypeId AND metricInstance = ?";
		
		try (Connection connect = DBConnectionManager.getInstance().getConnection();
				PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery);) {
			
			preparedStatement.setInt(1, metricInstanceId);
			
			try (ResultSet resultSet = preparedStatement.executeQuery()) {
				
				if(!resultSet.next()) {
					return null;
				}
				
				String typeName = resultSet.getString(METRIC_TYPE_NAME);
				int sourceCustId = resultSet.getInt(SOURCE_CUSTOMER);
				int targetCustId = resultSet.getInt(TARGET_CUSTOMER);
				int intData = resultSet.getInt(METRIC_INT_DATA);
				boolean hasInt = !resultSet.wasNull();
				Timestamp timestamp = resultSet.getTimestamp(METRIC_TIME, Calendar.getInstance(TimeZone.getTimeZone("UTC")));
				DateTime time = new DateTime(timestamp, DateTimeZone.UTC);
				
				Map<String, Integer> intMap = findIntValues(connect, metricInstanceId);
				Map<String, Float> floatMap = findFloatValues(connect, metricInstanceId);
				Map<String, String> stringMap = findStringValues(connect, metricInstanceId);
				
				if(hasInt) {
					intMap.put(METRIC_INT_DATA, intData);
				} 
				
				MetricsBO result = new MetricsBO(typeName, sourceCustId, targetCustId, time, intMap, floatMap, stringMap);
				result.setMetricInstanceId(metricInstanceId);
				
				return result;
			}
			
		} catch (SQLException e) {
			throw new ApplicationException(ReturnCodes.DATABASE_ERROR, "unable to get DB connection when MetricsMySQLDAO.insert");
		}
	}
	
	/**
	 * 
	 * @param connect sql connection
	 * @param metricInstanceId id of the metric
	 * @return all int values associated with the metric Id
	 */
	private Map<String, Integer> findIntValues(Connection connect, int metricInstanceId) {
		
		Map<String, Integer> intMap = new HashMap<>();
		
		String sqlQuery = "SELECT mi.metricValue, mv.metricValuename "
				+ " FROM  metricsint as mi INNER JOIN metricsvaluename as mv " 
				+ " WHERE mi.metricValueId = mv.metricValueId AND mi.metricInstance = ?";
		try (PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery)) { 

			preparedStatement.setInt(1, metricInstanceId);
			
			try (ResultSet resultSet = preparedStatement.executeQuery()) {
				while(resultSet.next())
				{
					int val = resultSet.getInt(METRIC_VALUE);
					String name = resultSet.getString(METRIC_VALUE_NAME);
					
					intMap.put(name, val);
				}
				
				return intMap;
			}
			
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.DATABASE_ERROR, "unable to get metric type for id" + metricInstanceId );
		} 
	}
	
	/**
	 * 
	 * @param connect sql connection
	 * @param metricInstanceId id of the metric
	 * @return all float values associated with the metric Id
	 */
	private Map<String, Float> findFloatValues(Connection connect, int metricInstanceId) {
		
		Map<String, Float> floatMap = new HashMap<>();
		
		String sqlQuery = "SELECT mf.metricValue, mv.metricValuename "
				+ " FROM  metricsfloat as mf INNER JOIN metricsvaluename as mv " 
				+ " WHERE mf.metricValueId = mv.metricValueId AND mf.metricInstance = ?";
		
		try (PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery)) { 
			
			preparedStatement.setInt(1, metricInstanceId);
			
			try (ResultSet resultSet = preparedStatement.executeQuery()) {
				while(resultSet.next())
				{
					Float val = resultSet.getFloat(METRIC_VALUE);
					String name = resultSet.getString(METRIC_VALUE_NAME);
					
					floatMap.put(name, val);
				}
				
				return floatMap;
			}
			
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.UNKNOWN_ERROR.toString(), "unable to get metric type for id" + metricInstanceId );
		}
	}
	
	
	/**
	 * 
	 * @param connect sql connection
	 * @param metricInstanceId id of the metric
	 * @return all float values associated with the metric Id
	 */
	private Map<String, String> findStringValues(Connection connect, int metricInstanceId) {
		
		Map<String, String> stringMap = new HashMap<>();
		
		String sqlQuery = "SELECT ms.metricValue, mv.metricValuename "
				+ " FROM  metricsstring as ms INNER JOIN metricsvaluename as mv " 
				+ " WHERE ms.metricValueId = mv.metricValueId AND ms.metricInstance = ?";
		
		try (PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery)) {

			preparedStatement.setInt(1, metricInstanceId);
			
			try (ResultSet resultSet = preparedStatement.executeQuery()) {
				while(resultSet.next())
				{
					String val = resultSet.getString(METRIC_VALUE);
					String name = resultSet.getString(METRIC_VALUE_NAME);
					
					stringMap.put(name, val);
				}
			
				return stringMap;
			}
			
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.DATABASE_ERROR, "unable to get metric type for id" + metricInstanceId );
		}
	}
}
